package com.cheksin.excl;

import com.cheksin.entity.ElemeAddGoods;
import com.cheksin.entity.Goods;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * 格式化导出excl
 * @author 大魔王
 *
 */
public class ExportElemeXlsx {
   private static final SimpleDateFormat SIMPLE_DATE_FORMAT = new SimpleDateFormat("yyyy-MM-dd");
   public static void main(String[] args) throws Exception{
       String[] header = {"名称（必填）", "分类（必填）", "条码", "主编码", "规格", "主单位", "库存量", "进货价（必填）", "销售价（必填）", "毛利率", "批发价", "会员价", "会员折扣", "积分商品",
               "库存上限", "库存下限", "库位", "品牌", "供货商", "生产日期", "保质期", "拼音码", "货号", "市场价", "过期日", "自定义3", "重量", "称编码", "商品状态", "商品描述", "创建日期"};
       exportExcl(header,genGoods(new File("d:\\商品资料.xlsx")),"d:\\商品资料-back.xls");

   }
   /**
    *银豹文件生成
    * @author 不愿透露
    * @date 2021/11/19 11:35
    * @param file
    * @return java.util.List<com.cheksin.excl.Goods>
    */
   public static List<?> genGoods(File file){
       List<Goods> goodsALL = ReadExcelUtil.readExcelInfo(file);
       List<ElemeAddGoods> elemeGoods = new ArrayList<>();
       elemeGoods.add(new ElemeAddGoods());
       for (Goods goods : goodsALL) {
           ElemeAddGoods elemeGood = new ElemeAddGoods();
           elemeGood.setSptxm(goods.getTm());
           elemeGood.setSpmc(goods.getTm());
           elemeGood.setSpmc(goods.getMc());
           //三级目录
           elemeGood.setSpsjml("");
           String name = goods.getMc();
           System.out.println(name);
           String zl = name.substring(name.lastIndexOf("(")+1, name.lastIndexOf(")"));
           String regEx="[^0-9]";
           Pattern p = Pattern.compile(regEx);
           Matcher m = p.matcher(zl);
           elemeGood.setZl(m.replaceAll("").trim());
           elemeGood.setSj(goods.getXsj());
           elemeGood.setKc(goods.getKcl());
           elemeGood.setSpzdqgj("1");
           if("启用".equals(goods.getSpzt())) {
               elemeGood.setSpzt("上架");
           }else {
               elemeGood.setSpzt("下架");
           }
           elemeGood.setDpyjflmc(goods.getFl());
           elemeGood.setDpejflmc("");
           elemeGood.setSpzdyid("");
           elemeGood.setHjh("");
           elemeGood.setXgkssj("");
           elemeGood.setXgjssj("");
           elemeGood.setXgzq("");
           elemeGood.setXgmznmrxgjs("");
           elemeGoods.add(elemeGood);
       }
       return elemeGoods;
    }

   /**
    *通用生成excl
    * @author 不愿透露
    * @date 2021/11/19 11:34
    * @param header 标题头
    * @param data 数据
    * @param exportPath 导出路径
    */
   public static void exportExcl(String[] header, List<?> data, String exportPath) {


       File file = new File(exportPath);
       if(file.exists()) {
           System.out.println("文件删除，重新创建！");
           file.delete();
       }
       String[] fieldNames = fieldName(Goods.class);
       Workbook wb = new HSSFWorkbook();
       int rowSize = 0;
       Sheet sheet = wb.createSheet();
       Row row = sheet.createRow(rowSize);
       for (int i = 0; i < header.length; i++) {
           row.createCell(i).setCellValue(header[i]);
       }

       try {
           for (int x = 0; x < data.size(); x++) {
               rowSize = 1;
               Row rowNew = sheet.createRow(rowSize + x);
               for (int i = 0; i < header.length; i++) {
                   Object user = data.get(x);
                   for (int i1 = 0; i1 < fieldNames.length; i1++) {
                       String methodName = "get" + fieldNames[i].substring(0,1).toUpperCase() + fieldNames[i].substring(1);//获取属性的get方法名
                       Method method = user.getClass().getMethod(methodName);
                       Object invoke = method.invoke(user);//获取属性值
                       invoke = invoke == null ? "" : invoke;
                       rowNew.createCell(i).setCellValue(invoke.toString());
                   }
               }
           }
       } catch (Exception e) {
           System.out.println(e);
       }
       OutputStream outputStream = null;
       try {
           outputStream = new FileOutputStream(exportPath);
           wb.write(outputStream);
       } catch (Exception e) {

       } finally {
           try {
               if (outputStream != null) {
                   outputStream.flush();
                   outputStream.close();
               }
           } catch (Exception e) {

           }
           try{
               if(wb != null){
                   wb.close();
               }
           } catch (Exception e){

           }
       }

   }

   public static String[] fieldName(Class clazz) {
       Field[] declaredFields = clazz.getDeclaredFields();
       String[] fieldNames = new String[declaredFields.length];
       for (int i = 0; i < declaredFields.length; i++) {
           fieldNames[i] = declaredFields[i].getName(); //通过反射获取属性名
       }
       return fieldNames;
   }
}